OfficeHelp - Office Tutorials & Software Solutions
Search:
 
Advanced Search

 
Get FREE PC TIPS every month:

More Information Here

Most Popular

What's available for:
Calendars
Privacy & Security
Excel
Outlook
Word
PowerPoint
Internet Explorer
Others


Calendar Plan Generator - BASIC Edition

Easily generate Project style calendar plans, just by entering the tasks and associated data like it's name and the starting and end date. Use different colors and select a deadline terminator for each task. Indent each task to its level or sub-level. Change, save and email the resulting calendar as any other regular Microsoft® Excel file. The SIMPLEST edition, just ONE button to use!


Other Contents
Design Chart Templates for Microsoft® Excel®
Automatically update hundreds of PowerPoint slides from Excel in minutes
Quickly and easily generate your own Org Charts in Excel
Easily make your own home and office calendars for Microsoft® Excel®!
Calendar Templates with Holidays (USA / UK / Australia / Canada) for Excel
Make stylish Planner Calendars - CUSTOM Version
All CALENDARS at once!
Calendar Templates 2007 with Holidays for Excel
Make stylish Planner Calendars - ADVANCED Version
All TEMPLATES at once!
Make stylish Planner Calendars
Calendar Plan - Year Planner Template
How long the wait?

How to Buy
How safe it is to buy
Privacy Policy
Contact Us

RSS Feed OfficeHelp.Biz RSS Feed
Home Calendars Bundles Macros PC Tips Templates Newsletter Contact Us

Specializing in Advanced Spreadsheet & Office Macro Solutions.


Content Type Application Author Price
PC Tip Microsoft® Office® General OfficeHelp Free

Create YOUR OWN Microsoft® Office Macros - Version 1.00

Tested on Microsoft® Office® General Versions: 2003 (11.0) - should work on any Windows version from 2000


 

Macros are software applications that run inside other applications, like Office packages. While some very sophisticated macros are real pieces of Software, demanding professional developers, ANYONE can create their own automation macros in any of the Microsoft® Office applications.


Macros are software applications
that run inside other applications, like Office packages. In fact, many commercial software's now include some form of end-user programming to allow for customization and automations of common tasks. Microsoft's VBA (Visual Basic for Applications) is the macro language bundled into Office and currently licensed to many other software makers.

They are very useful to extend the usefulness and productivity of the Office applications. As an example, in this website you can find, among others, Macros that build calendar plans using Excel and no extra software.

Macros exist for all current Office applications, but only the most recent versions of Outlook (from 2000) support them (previous version have a different programming model). This article will use EXCEL for illustrations purposes, but in general its concepts will apply to all Microsoft® Office Applications.

Setting Macro Security

Macros are potentially dangerous. Since they are software and run by the PC user, they can do almost anything on the computer they're running, including erasing the hard drive and other forms of damage. And since they are imbedded in office applications, they can be mailed as regular office files. In fact, one of the first virus to use email to spread was the Melissa Virus, a Word Macro! Of course, back in 1999, email virus were just starting and nobody expected them, so email was considered safe and no defenses were in place.

Click here for a full article on Office macro security in this website.

IMPORTANT NOTE: If you have trouble running macros when following this article, please refer to the article above and adjust your security settings before proceeding.

Automation and Coded Macros

There are basically two types of macros, even if the system itself won’t make a difference between them. The difference is, really, in the usefulness and extent of the macro:

  • Automation macros are macros that automate a set of manual operations – allowing users to avoid having to manually repeat an operation. These macros are usually not coded but recorded, using the Macro Recorder.
  • Coded macros are macros that are written by software developers and that, in reality, are peaces of software that run (and require) a hosting application to run – Like Excel.

For an example of a Coded macro, that is too complex to be generated by recording an example, click here to download the demo of our Calendar Plan Generator macro.

This article is about generating Automation macros, the ones that ANY user can create, even if they don’t write a single line of software!

Generating your own automation Macros

To generate a Macro, first you need an operation to be performed. As an example, lets generate a macro that:

  1. Applies a light border to the all selected cells
  2. Applies a heavier border to the outline (outer border) of the selected cell

Macro Effect on Excel Cells
PIC 1 – Macro effect on Excel cells

To record a macro that performs this operation, you only need to do it yourself while having the Macro Recorder activated (to record your operations):

1) Select a cell area (you have to do it before you start recording, otherwise your selection will be saved on the macro and will always be repeated – not very useful, you want the macro to apply to any cell selection)

Start recording a New Macro

2) On the Tools menu, select Macro and “Record New Macro”:

  • Give the Macro a meaningful name (related do function) like “Grid Maker”
  • Assign a Shortcut Key so that the macro can be invoked later from the keyboard (lets use M)
  • Leave the Store Macro In option in its default setting: In this Workbook
  • Add a description for future reference
  • Click OK

Record Macro PopUp

3) Perform the desired operations. In this case:

  • Apply a light border to all selected cells
  • Apply a heavier border to the outline

4) Click on the stop button of the macro recorder toolbar (PIC):

The folder is hidden

You now have a macro that will apply the border to ANY cell selection.

Running the Macro

In case you haven’t performed the steps above, just use our ready to use demo:

Click here to download this TIP FREE demo

To run the macro, you have several options:

  • Use the Keyboard shortkey (only if you have defined one, not all macros have them): Pres CTRL + (SHIFT ) + Macro key (in our sample, CTRL + SHIFT + M)
  • Run it from the Tools Menu: Select Macros, Macros again.
    • Choose your macro
    • Click on the Run Button

Play existing macros

Editing the Macro Code

If you know VBA (or Visual Basic, VBScript or any other related language) you may want to manually edit the macro. Or you may just be curious about what was exactly recorded by the macro. To open the macro editor, press ALT+F11 on your keyboard. You will enter the Macro Editor:

Macro Code Editor - Click to Enlarge

PIC 6: Macro (VBA) Editor (Click to Enlarge).

Recorder Macros usually reside on Module1, on the Modules section. One useful manual addition to your code (that cannot be done with the recorder) is adding a message box to indicate the macro has finished. Add a line to the end of your macro code (before the “End Sub”) with

MSGBOX “The macro nnnnn has finished”, vbOKOnly, “MY MACRO TITLE”

Where nnnnn is the name of your macro. The general format for this command is:

MSGBOX “message body”, button codes, “TITLE”

Check the DEMO for an example. Click here to download the DEMO

You may experiment with changing the code. To avoid destroying it, you may save the original macro, create a copy and edit the copy. If you end up with an unusable macro, you may just delete the copy and make a new one from the original macro.

Saving the Macros for future use

Macros are usually saved with the file where they were recorded. This mean they will also be available on that file, regardless of the computer used to open it. So, if you email the file to a friend, the macro will be there.

To save the macro with the file, just save the file with the Save command. It will be saved with your data and all other file contents (Charts, formats, etc).

Saving the macro as a AddIn: For use in multiple files

This option may not be available for all Microsoft Office applications, but is available in Excel. You may save your macro as a AddIn that its loaded with Excel every time it starts. This way, if the macro is generic and can be useful in many files, it will always be available regardless of the file currently being edited.

The drawback is that in this format, the macro behaved as an Excel extension on that computer and, therefore, will not be available on other computers (unless specifically installed on them) and will not travel with the data files (if you email a file to a friend, the macro will not be there for him because it is installed on your PC, not the data file).

To save it as an AddIn, do this:

1) Select Save As on the File menu

2) On the “Save as Type” box select “Microsoft Excel Add-In (*.xla)

3) On the most recent versions of Excel, you will be automatically positioned on the AddIns folder. On versions prior to 2000, you need to find the AddIns folder yourself

4) Give it a name and save it

Save as AddIn

From now on, your addin will load with Excel and the macro will be available every time you run Excel, but only on the PC where it has been saved.

To install it on other computers, just mail them the original Excel macro file and repeat the steps above on each one.

Try the FREE Demo

In case you haven’t performed the steps above, you may still experiment the usefulness of macros. Just use our ready to use demo:

Click here to download this TIP FREE demo

Can I know more about this subject ?

Check our FREE PC Tips. They will be regularly published. Better still, subscribe our Email Newsletter. You will be informed when contents updates are made and automatically granted participation on any future promotion.

Get FREE PC TIPS every month:

 


Create YOUR OWN Microsoft® Office Macros - Version 1.00

Tested on Microsoft® Office® General Versions: 2003 (11.0) - should work on any Windows version from 2000


Back
Other Contents  

00052- Design Chart Templates for Microsoft® Excel® Microsoft Excel Templates
The easiest way to create design charts. Check the samples, enter your own data and watch as your charts update. Multiple different charts from the same data, with many automatic calculations made for you. No code, macros, formulas or other complicated stuff. Just fill in YOUR data and watch the charts update automatically.
 
00054- Automatic Copy/Paste for Microsoft PowerPoint® Microsoft Excel Macro
Automatically update hundreds of Microsoft PowerPoint® slides, charts and tables from Excel in minutes. With the click on a button. Easily configure the updates you need. Copy from multiple Excel files. Resize and place pasted charts & tables – or just keep their original size & position.
 
00051- Organization Chart Maker for Microsoft® Excel® Microsoft Excel Macro
Quickly and easily generate your own organization charts from a plain list of data with Organization Chart Maker for Microsoft® Excel®. Apply colors and formats. Integrate external data from ERP or HR management systems like or Microsoft® Dynamics Navision, SAP®, PeopleSoft®, Oracle®, etc. Chart Stock & Portfolio listings, team members planning & company ownership charts.
 
00037- Traditional Calendars for Excel Microsoft Excel Macro
Easily generate annual and monthly Traditional & Planning calendars you can print and distribute, based only in Microsoft® Excel®. Five different formats available. Use special colors for special days or events, like holidays or meetings. Automatic event legend. Configurable background and font colors. Optional calendar background image. Traditional, Academic and Fiscal years. Imports data from Microsoft ® Outlook®.
 
00047- Calendar Templates 2014 with Holidays (USA / UK / Australia / Canada) for Excel Microsoft Excel Templates
Yearly, Monthly and Weekly Calendar templates (2 each) for Microsoft® Excel®. Includes versions with Holidays for the USA, UK, Australia and Canada.
 
00044- Custom GANTT Charts for Microsoft® Excel® Microsoft Excel Macro
Generate CUSTOM Project style calendar plans with the EXCLUSIVE option to customize our COPYRIGHT messages to create YOUR own REBRANDED Edition. Includes all ADVANCED Edition features plus the REBRANDING option. Distribute CORPORATE versions to your staff.
 
00027- Get all OfficeHelp.Biz CALENDARS at once! Microsoft Excel Bundles
Interested in more than one of our Microsoft® Excel® Calendars? Don't want the hassle of paying per purchase? Want to SAVE TIME AND MONEY?
 
00046- Calendar Templates 2007 with Holidays for Excel Microsoft Excel Templates
Yearly, Monthly and Weekly Calendar templates (2 each) for 2007 in Excel. Include USA Holidays.
 
00002- Calendar Plan Generator - ADVANCED Edition Microsoft Excel Macro
Generate ADVANCED Project style calendar plans, just by entering the tasks and associated data like its name and the starting and end date. Use different colors and select a deadline terminator for each task. Indent each task to its level or sub-level. Change, save and email the resulting calendar as any other regular Microsoft® Excel® file. Includes ADVANCED features like EXTRA plan types, detail filtering, optional fields, extra information of task bars, full customization and Microsoft® Outlook® integration.
 
00040- Get all OfficeHelp.Biz TEMPLATES at once! Microsoft Excel Bundles
Interested in more than one of our Templates, including all Excel Calendar Templates? Don't want the hassle of paying per purchase? Want to SAVE TIME AND MONEY?
 
00036- Calendar Plan Generator - Source Code Edition Microsoft Excel Macro
Generate Project style calendar plans (weekly only), just by entering the tasks and associated data like it's name and the starting and end date. Use different colors and select a deadline terminator for each task. Indent each task to its level or sub-level. Change, save and email the resulting calendar as any other regular Microsoft® Excel file. SOURCE CODE available for changes for VBA Macro developers.
 
00043- Calendar Plan Generator - BASIC Edition Microsoft Excel Macro
Easily generate Project style calendar plans, just by entering the tasks and associated data like it's name and the starting and end date. Use different colors and select a deadline terminator for each task. Indent each task to its level or sub-level. Change, save and email the resulting calendar as any other regular Microsoft® Excel file. The SIMPLEST edition, just ONE button to use!
 
00028- Calendar Plan - Year Planner Template Microsoft Excel Templates
Create pretty yearlong planning calendars in minutes. Save time and effort. Ready to use calendar template! Really useful to set yearlong event like general meetings, special groups, holidays and all other long-term schedules. Regular Excel file, sample included.
 
00001- Queue Servicing Calculator Microsoft Excel Macro
Calculates the average waiting times and people (or item) processing times based on some input estimates. Can be used to make service capacity simulations or to measure the service throughput on any service or process demanding waiting queues.
 

Privacy Policy How to Buy How safe is it to Buy? RSS Feed Contact Us

(c) 2004 - 2024 OfficeHelp.Biz